IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncDatabaseFileMSSQL' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncDatabaseFileMSSQL
GO

CREATE PROCEDURE dbo.SaveSrvrInstncDatabaseFileMSSQL
     @SrvrInstncDatabaseId          int,
     @PackageExecutionId            bigint,
     @DatabaseFileId                int              = NULL,
     @DatabaseFileGUID              uniqueidentifier = NULL,
     @FileIsLogFileFlag             tinyint          = NULL,
     @FileSizeInMB                  int              = NULL,
     @MaxFileSizeInMB               int              = NULL,
     @FileGrowthInMB                int              = NULL,
     @FileGrowthInPct               tinyint          = NULL,
     @FileGroupName                 varchar(128)     = NULL,
     @LogicalFileName               varchar(128)     = NULL,
     @PhysicalFileNameWithPath      varchar(255)     = NULL,
     @FileDeletedFlag               tinyint          = 0,
     @SrvrInstncDatabaseFileMSSQLId int              = NULL
AS
/*
 *
 * SaveSrvrInstncDatabaseFileMSSQL
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/09/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @MaxPEI  bigint,
             @RC      int,
             @NOW     datetime,
             @StartDt datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 

     -- Condition Flag
     SELECT @FileIsLogFileFlag = CASE WHEN @FileIsLogFileFlag != 1 OR @FileIsLogFileFlag IS NULL THEN 0 ELSE 1 END

     IF ( @FileDeletedFlag != 1 )
     BEGIN
          SELECT @SrvrInstncDatabaseFileMSSQLId = dbo.GetDatabaseFileMSSQLIdForServerDatabase
                                                        ( @SrvrInstncDatabaseId, 
                                                          @DatabaseFileId, 
                                                          @DatabaseFileGUID, 
                                                          @FileIsLogFileFlag, 
                                                          @LogicalFileName, 
                                                          @PhysicalFileNameWithPath )
     END

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncDatabaseFileMSSQL
                     WHERE  SrvrInstncDatabaseId          = @SrvrInstncDatabaseId
                     AND    SrvrInstncDatabaseFileMSSQLId = @SrvrInstncDatabaseFileMSSQLId )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          INSERT dbo.SrvrInstncDatabaseFileMSSQL
               ( SrvrInstncDatabaseFileMSSQLId,
                 SrvrInstncDatabaseId,
                 PackageExecutionId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 DatabaseFileId,
                 DatabaseFileGUID,
                 FileIsLogFileFlag,
                 FileSizeInMB,
                 MaxFileSizeInMB,
                 FileGrowthInMB,
                 FileGrowthInPct,
                 FileGroupName,
                 LogicalFileName,
                 PhysicalFileNameWithPath )
          SELECT @SrvrInstncDatabaseFileMSSQLId,
                 @SrvrInstncDatabaseId,
                 @PackageExecutionId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @DatabaseFileId,
                 @DatabaseFileGUID,
                 @FileIsLogFileFlag,
                 @FileSizeInMB,
                 @MaxFileSizeInMB,
                 @FileGrowthInMB,
                 @FileGrowthInPct,
                 @FileGroupName,
                 @LogicalFileName,
                 @PhysicalFileNameWithPath
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.SrvrInstncDatabaseFileMSSQL
          WHERE  SrvrInstncDatabaseId          = @SrvrInstncDatabaseId
          AND    SrvrInstncDatabaseFileMSSQLId = @SrvrInstncDatabaseFileMSSQLId
          AND    CurrentRecordFlag             = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.SrvrInstncDatabaseFileMSSQL
          SET    EndDt             = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag = 0
          WHERE  SrvrInstncDatabaseId          = @SrvrInstncDatabaseId
          AND    SrvrInstncDatabaseFileMSSQLId = @SrvrInstncDatabaseFileMSSQLId
          AND    CurrentRecordFlag             = 1

          IF ( @FileDeletedFlag = 0 )
          BEGIN -- Do regular checked insert
               INSERT dbo.SrvrInstncDatabaseFileMSSQL
                    ( SrvrInstncDatabaseFileMSSQLId,
                      SrvrInstncDatabaseId,
                      PackageExecutionId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      FileDeletedFlag,
                      DatabaseFileId,
                      DatabaseFileGUID,
                      FileSizeInMB,
                      MaxFileSizeInMB,
                      FileIsLogFileFlag,
                      FileGrowthInMB,
                      FileGrowthInPct,
                      FileGroupName,
                      LogicalFileName,
                      PhysicalFileNameWithPath )
               SELECT @SrvrInstncDatabaseFileMSSQLId,
                      @SrvrInstncDatabaseId,
                      @PackageExecutionId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      FileDeletedFlag = 0,
                      CASE WHEN DatabaseFileId           IS NOT NULL AND @DatabaseFileId           IS NULL THEN DatabaseFileId           ELSE @DatabaseFileId           END,
                      CASE WHEN DatabaseFileGUID         IS NOT NULL AND @DatabaseFileGUID         IS NULL THEN DatabaseFileGUID         ELSE @DatabaseFileGUID         END,
                      CASE WHEN FileSizeInMB             IS NOT NULL AND @FileSizeInMB             IS NULL THEN FileSizeInMB             ELSE @FileSizeInMB             END,
                      CASE WHEN MaxFileSizeInMB          IS NOT NULL AND @MaxFileSizeInMB          IS NULL THEN MaxFileSizeInMB          ELSE @MaxFileSizeInMB          END,
                      @FileIsLogFileFlag,
                      @FileGrowthInMB,
                      @FileGrowthInPct,
                      CASE WHEN FileGroupName            IS NOT NULL AND @FileGroupName            IS NULL THEN FileGroupName            ELSE @FileGroupName            END,
                      CASE WHEN LogicalFileName          IS NOT NULL AND @LogicalFileName          IS NULL THEN LogicalFileName          ELSE @LogicalFileName          END, 
                      CASE WHEN PhysicalFileNameWithPath IS NOT NULL AND @PhysicalFileNameWithPath IS NULL THEN PhysicalFileNameWithPath ELSE @PhysicalFileNameWithPath END
               FROM   dbo.SrvrInstncDatabaseFileMSSQL
               WHERE  SrvrInstncDatabaseId          = @SrvrInstncDatabaseId
               AND    SrvrInstncDatabaseFileMSSQLId = @SrvrInstncDatabaseFileMSSQLId
               --AND    PackageExecutionId            = @MaxPEI
               AND    StartDt                       = @StartDt
               -- Decide if there will be an insert
               AND  ( FileDeletedFlag = 1
                      -- 1 = 0 -- Placeholder, always false, don't want to insert on accident
                   OR ( @DatabaseFileId IS NOT NULL           AND DatabaseFileId IS NULL )
                   OR ( @DatabaseFileGUID IS NOT NULL         AND DatabaseFileGUID IS NULL )
                   OR ( @FileSizeInMB IS NOT NULL             AND FileSizeInMB IS NULL ) 
                   OR ( @MaxFileSizeInMB IS NOT NULL          AND MaxFileSizeInMB IS NULL )
                   OR ( @FileGroupName IS NOT NULL            AND FileGroupName IS NULL ) 
                   OR ( @LogicalFileName IS NOT NULL          AND LogicalFileName IS NULL )
                   OR ( @PhysicalFileNameWithPath IS NOT NULL AND PhysicalFileNameWithPath IS NULL )
                   -- These two are kind of a special case, they can actually 
                   -- flip from one to the other so we want to be sure they can.
                   OR ( @FileGrowthInMB IS NOT NULL           AND FileGrowthInMB IS NULL )
                   OR ( @FileGrowthInMB IS NULL               AND FileGrowthInMB IS NOT NULL )
                   OR ( @FileGrowthInPct IS NOT NULL          AND FileGrowthInPct IS NULL )
                   OR ( @FileGrowthInPct IS NULL              AND FileGrowthInPct IS NOT NULL )
                   OR ( @DatabaseFileId                       != DatabaseFileId )
                   OR ( @DatabaseFileGUID                     != DatabaseFileGUID )
                   OR ( @FileSizeInMB                         != FileSizeInMB )
                   OR ( @MaxFileSizeInMB                      != MaxFileSizeInMB )
                   OR ( @FileGrowthInMB                       != FileGrowthInMB )
                   OR ( @FileGrowthInPct                      != FileGrowthInPct )
                   OR ( @FileGroupName                        != FileGroupName )
                   OR ( @LogicalFileName                      != LogicalFileName )
                   OR ( @PhysicalFileNameWithPath             != PhysicalFileNameWithPath )
                    ) -- End of And-Or section

               SELECT @RC = @@ROWCOUNT 
          END -- End of regular checked insert
          ELSE
          BEGIN -- Do database deleted insert
               IF EXISTS ( SELECT 1 
                           FROM   dbo.SrvrInstncDatabaseFileMSSQL
                           WHERE  SrvrInstncDatabaseId          = @SrvrInstncDatabaseId
                           AND    SrvrInstncDatabaseFileMSSQLId = @SrvrInstncDatabaseFileMSSQLId
                           AND    StartDt                       = @StartDt
                           AND    FileDeletedFlag               = 0 )
               BEGIN -- Not already deleted.
                    INSERT dbo.SrvrInstncDatabaseFileMSSQL
                         ( SrvrInstncDatabaseFileMSSQLId,
                           SrvrInstncDatabaseId,
                           PackageExecutionId,
                           StartDt,
                           EndDt,
                           CurrentRecordFlag,
                           FileDeletedFlag,
                           DatabaseFileId,
                           DatabaseFileGUID,
                           FileIsLogFileFlag,
                           FileSizeInMB,
                           MaxFileSizeInMB,
                           FileGrowthInMB,
                           FileGrowthInPct,
                           FileGroupName,
                           LogicalFileName,
                           PhysicalFileNameWithPath )
                    SELECT SrvrInstncDatabaseFileMSSQLId,
                           SrvrInstncDatabaseId,
                           @PackageExecutionId,
                           @NOW, -- StartDt
                           '12/31/9999', -- EndDt
                           1, -- CurrentRecordFlag
                           FileDeletedFlag = 1,
                           DatabaseFileId,
                           DatabaseFileGUID,
                           FileIsLogFileFlag,
                           FileSizeInMB,
                           MaxFileSizeInMB,
                           FileGrowthInMB,
                           FileGrowthInPct,
                           FileGroupName,
                           LogicalFileName,
                           PhysicalFileNameWithPath
                    FROM   dbo.SrvrInstncDatabaseFileMSSQL
                    WHERE  SrvrInstncDatabaseId          = @SrvrInstncDatabaseId
                    AND    SrvrInstncDatabaseFileMSSQLId = @SrvrInstncDatabaseFileMSSQLId
                    --AND    PackageExecutionId            = @PEI
                    AND    StartDt                       = @StartDt

                    SELECT @RC = @@ROWCOUNT 
               END
          END

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance database file for MSSSQL record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncDatabaseFileMSSQL;
GO
